﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using PetaPoco;
using System.Data;
using System.Web;
using System.Text.RegularExpressions;

namespace PetaPoco
{
    #region ext

    public static class OperateTypeExt
    {
        public static string GetOperate(this OperateType operate)
        {
            switch (operate)
            {
                case OperateType.Eq:
                    return " = ";
                case OperateType.Less:
                    return " < ";
                case OperateType.Great:
                    return " > ";
                case OperateType.LessEq:
                    return " <= ";
                case OperateType.GreatEq:
                    return " >= ";
                case OperateType.Like:
                    return " LIKE ";
                case OperateType.In:
                    return " IN ";
                default:
                    return " = ";
            }
        }
    }
    #endregion

    #region ScriptSearch Model

    public enum OperateType
    {
        Eq = 1,

        Less = 2,
        Great = 3,

        LessEq = 4,
        GreatEq = 5,

        Like = 6,

        In = 7
    }

    public class ScriptSearch
    {
        public string TableName
        {
            get;
            set;
        }
        public string Column
        {
            get;
            set;
        }
        public OperateType Operate
        {
            get;
            set;
        }
        public string Value
        {
            get;
            set;
        }
    }
    #endregion

    /// <summary>
    /// DataTable分页实体
    /// </summary>
    public partial class PageTable
    {
        public long CurrentPage { get; set; }
        public long TotalPages { get; set; }
        public long TotalItems { get; set; }
        public long ItemsPerPage { get; set; }
        public DataTable Table { get; set; }
        public object Context { get; set; }
    }

    public partial class Database
    {
        /// <summary>
        /// 分页，返回DataTable数据格式
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="page">页</param>
        /// <param name="itemsPerPage">页条数</param>
        /// <param name="scriptSearchs">搜索条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns>分页结果</returns>
        public PageTable PageTable<T>(long page, long itemsPerPage, List<ScriptSearch> scriptSearchs, string orderBy)
        {
            Sql SqlObject = GetSql(scriptSearchs, orderBy);

            return PageTable<T>(page, itemsPerPage, SqlObject.SQL, SqlObject.Arguments);
        }

        /// <summary>
        /// 分页，返回DataTable数据格式
        /// </summary>
        /// <typeparam name="T">类型</typeparam>
        /// <param name="page">页</param>
        /// <param name="itemsPerPage">页条数</param>
        /// <param name="sql">sql语句</param>
        /// <param name="args">传参</param>
        /// <returns>分页结果</returns>
        public PageTable PageTable<T>(long page, long itemsPerPage, string sql, params object[] args)
        {
            //Match match = Regex.Match(sql,
            //    @"((?<open>\()|(?<-open>\))|[^()])*(?<tt>where)(?(open)(?!))",
            //    RegexOptions.IgnoreCase | RegexOptions.Compiled);
            //if (match.Success)
            //{ 
            //}
            if (sql.LastIndexOf(')') > 0 && sql.Substring(sql.LastIndexOf(')')).IndexOf("WHERE", StringComparison.OrdinalIgnoreCase) > -1)
            {
                int startIndex = sql.IndexOf("WHERE", StringComparison.OrdinalIgnoreCase);
                int endIndex = sql.LastIndexOf("WHERE", StringComparison.OrdinalIgnoreCase);
                if (startIndex != endIndex)
                    sql = sql.Substring(0, endIndex) + "AND" + sql.Substring(endIndex + 5);
            }

            string sqlCount, sqlPage;
            BuildPageQueries<T>((page - 1) * itemsPerPage, itemsPerPage, sql, ref args, out sqlCount, out sqlPage);

            // Save the one-time command time out and use it for both queries
            int saveTimeout = OneTimeCommandTimeout;

            // Setup the paged result
            var result = new PageTable();
            result.CurrentPage = page;
            result.ItemsPerPage = itemsPerPage;

            if (HttpContext.Current != null && HttpContext.Current.Request.Params.AllKeys.Contains("rowCount"))
                result.TotalItems = Convert.ToInt64(HttpContext.Current.Request.Params["rowCount"]);

            if (result.TotalItems <= 0)
                result.TotalItems = ExecuteScalar<long>(sqlCount, args);

            result.TotalPages = result.TotalItems / itemsPerPage;
            if ((result.TotalItems % itemsPerPage) != 0)
                result.TotalPages++;

            OneTimeCommandTimeout = saveTimeout;

            result.Table = Table(sqlPage, args);

            // Done
            return result;
        }

        /// <summary>
        /// 根据搜索条件返回sql(注意：弃用)
        /// </summary>
        /// <param name="scriptSearchs">条件</param>
        /// <param name="orderBy">排序</param>
        /// <returns>sql</returns>
        public Sql GetSql(List<ScriptSearch> scriptSearchs, string orderBy)
        {
            Sql sql = new Sql();
            for (int i = 0; i < scriptSearchs.Count; i++)
            {
                var scriptSearch = scriptSearchs[i];

                if (!string.IsNullOrEmpty(scriptSearch.TableName))
                    scriptSearch.Column = scriptSearch.TableName + "." + scriptSearch.Column;

                if (scriptSearch.Column.Contains(' ') || scriptSearch.Column.Contains(';'))
                    continue;

                if (scriptSearch.Operate != OperateType.In)
                    sql.Where(scriptSearch.Column + scriptSearch.Operate.GetOperate() + "@0", GetOperateValueText(scriptSearch.Operate, scriptSearch.Value));
                else
                    sql.Where(scriptSearch.Column + scriptSearch.Operate.GetOperate() + GetOperateValueText(scriptSearch.Operate, scriptSearch.Value));
            }
            if (!string.IsNullOrEmpty(orderBy))
                sql.OrderBy(orderBy);
            return sql;
        }


        /// <summary>
        /// 根据不同的操作方式，返回对应的格式化值
        /// </summary>
        /// <param name="operateType">操作类型</param>
        /// <param name="value">值</param>
        /// <returns></returns>
        private object GetOperateValueText(OperateType operateType, string value)
        {
            switch (operateType)
            {
                case OperateType.Like:
                    return "%" + value + "%";
                case OperateType.In:
                    return "(" + value + ")";
                default:
                    if (Regex.IsMatch(value, @"^[0-9]+$", RegexOptions.Compiled))
                        return int.Parse(value);
                    return value;
            }
        }
    }
}
